package com.teamwings.common.jsqlparser;

import com.teamwings.common.constant.SqlParserConstant;
import com.teamwings.common.exception.ResultException;
import com.teamwings.common.jsqlparser.schema.ParsedStructureQuery;
import com.teamwings.common.jsqlparser.schema.SelectColumn;
import com.teamwings.common.jsqlparser.schema.SelectTable;
import com.teamwings.common.jsqlparser.schema.StructureQuery;
import com.teamwings.modules.page.service.IPageService;
import com.teamwings.util.SpringContextUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.data.redis.core.RedisTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.stream.Collectors;

public class SelectParseHelper {

    private static RedisTemplate<String, Object> redisTemplate;

    private static RedisTemplate<String, Object> getRedisTemplate() {
        if (redisTemplate == null) {
            redisTemplate = (RedisTemplate<String, Object>) SpringContextUtil.getBean("redisTemplate");
        }
        return redisTemplate;
    }

    /**
     * 获取根据sql获取字段
     *
     * @param sql 要解析的SQL语句
     * @return List<String>
     */
    public static List<String> getColumnBySql(IPageService pageService, String sql) throws JSQLParserException, ResultException {
        ParsedStructureQuery parsedStructureQuery = getParsedStructureQuery(pageService, sql);
        List<String> columns = parsedStructureQuery.getColumnList();
        return columns.stream().map(column -> column.toLowerCase(Locale.ROOT)).collect(Collectors.toList());
    }

    public static List<String> getOriginalColumnBySql(IPageService pageService, String sql) throws JSQLParserException, ResultException {
        ParsedStructureQuery parsedStructureQuery = getParsedStructureQuery(pageService, sql);
        List<SelectColumn> selectColumnList = parsedStructureQuery.getSelectColumnList();
        ArrayList<String> columns = new ArrayList<>();
        for (SelectColumn selectColumn : selectColumnList) {
            String originalColumn = selectColumn.getOriginalColumn();
            columns.add(originalColumn);
        }
        return columns.stream().map(column -> column.toLowerCase(Locale.ROOT)).collect(Collectors.toList());
    }


    /**
     * 获取解析结构查询
     * @param pageService 里面提供了诸如表名查询表列等方法
     * @param sql sql字符
     * @return ParsedStructureQuery对象该对象是一个树型结构
     */
    public static ParsedStructureQuery getParsedStructureQuery(IPageService pageService, String sql) {
        if (StringUtils.isEmpty(sql)) {
            throw new ResultException("sql语句不能为空");
        }
        sql = sql.trim();

        String redisCacheKey = SqlParserConstant.SQL_PARSER_STRUCTURE_QUERY + sql;

        if (getRedisTemplate().hasKey(redisCacheKey)) {
            return (ParsedStructureQuery) getRedisTemplate().opsForValue().get(redisCacheKey);
        }

        Statement stmt = null;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
        Select selectStatement = null;
        if (stmt instanceof Select) {
            selectStatement = (Select) stmt;
        } else {
            throw new ResultException("只能解析Select语句(查询语句)");
        }

        AutoFormSelectVisitor autoFormSelectVisitor = new AutoFormSelectVisitor();
        SelectBody sBody = selectStatement.getSelectBody();
        sBody.accept(autoFormSelectVisitor);

        StructureQuery structureQuery = autoFormSelectVisitor.getStructureQuery();
        Boolean hasWhere = autoFormSelectVisitor.getHasWhere();
        Boolean hasLimit = autoFormSelectVisitor.getHasLimit();
        Boolean hasOrderBy = autoFormSelectVisitor.getHasOrderBy();

        // 尝试性执行一下sql,如果执行有问题可以报告给用户
        try {
            if (!hasLimit) {
                pageService.executeSql(sql + " limit 1");
            } else {
                pageService.executeSql(sql);
            }
        } catch (Exception e) {
            throw new ResultException(e.getMessage(), e);
        }

        List<String> columns = deepStructureQuery(pageService, structureQuery);

        // 检测重复字段
        List<String> tempColumns = new ArrayList<>();
        for (String column : columns) {
            if (column.contains(".")) {
                String columnName = column.split("\\.")[1];
                if (tempColumns.contains(columnName)) {
                    throw new ResultException("检测到sql语句中存在字段重复，请为“" + column + "”字段重命名");
                } else {
                    tempColumns.add(columnName);
                }
            } else {
                tempColumns.add(column);
            }
        }

        ParsedStructureQuery parsedStructureQuery = new ParsedStructureQuery();
        BeanUtils.copyProperties(structureQuery, parsedStructureQuery);
        parsedStructureQuery.setColumnList(columns);
        parsedStructureQuery.setSql(sql);
        parsedStructureQuery.setHasWhere(hasWhere);
        parsedStructureQuery.setHasLimit(hasLimit);
        parsedStructureQuery.setHasOrderBy(hasOrderBy);

        getRedisTemplate().opsForValue().set(redisCacheKey, parsedStructureQuery);

        return parsedStructureQuery;
    }

    public static boolean hasWhere(IPageService pageService, String sql) {
        ParsedStructureQuery parsedStructureQuery = getParsedStructureQuery(pageService, sql);
        return parsedStructureQuery.isHasWhere();
    }

    public static boolean hasOrderBy(IPageService pageService, String sql) {
        ParsedStructureQuery parsedStructureQuery = getParsedStructureQuery(pageService, sql);
        return parsedStructureQuery.isHasOrderBy();
    }

    private static List<String> deepStructureQuery(IPageService pageService, StructureQuery structureQuery) {
        List<String> columnList = new ArrayList<>();

        List<SelectColumn> selectColumnList = structureQuery.getSelectColumnList();
        List<SelectTable> selectTableList = structureQuery.getSelectTableList();

        for (SelectColumn selectColumn : selectColumnList) {
            String aliasColumn = selectColumn.getAliasColumn();
            String originalColumn = selectColumn.getOriginalColumn();

            if (originalColumn.equals("*")) {
                for (SelectTable selectTable : selectTableList) {
                    Boolean isSubSelect = selectTable.getSubSelect();
                    String tableName = selectTable.getName();
                    String tableAliasName = selectTable.getAliasName();

                    if (isSubSelect) {
                        List<StructureQuery> subSelectList = structureQuery.getSubSelectList();
                        for (StructureQuery children : subSelectList) {
                            List<String> columns = deepStructureQuery(pageService, children);
                            columns = columns.stream().map(column ->
                                    column = column.contains(".")
                                    ? column = column.replace(column.substring(0, column.indexOf(".")), tableAliasName)
                                    : tableAliasName + "." + column
                            ).collect(Collectors.toList());
                            columnList.addAll(columns);
                        }
                    } else {
                        // 不是子查询
                        List<String> columnByTableName = pageService.getColumnByTableName(tableName);
                        if (selectTableList.size() > 1) {
                            if (StringUtils.isNotEmpty(tableAliasName)) {
                                columnByTableName = columnByTableName.stream()
                                        .map(column -> column = tableAliasName + "." + column)
                                        .collect(Collectors.toList());
                            } else {
                                columnByTableName = columnByTableName.stream()
                                        .map(column -> column = tableName + "." + column)
                                        .collect(Collectors.toList());
                            }
                        }
                        columnList.addAll(columnByTableName);
                    }
                }
            } else if (originalColumn.contains(".*")) {
                String[] temp = originalColumn.split("\\.");
                String columnTableName = temp[0];
                for (SelectTable selectTable : selectTableList) {
                    String tableAliasName = selectTable.getAliasName();
                    String tableName = selectTable.getName();
                    Boolean isSubSelect = selectTable.getSubSelect();
                    if ((StringUtils.isNotEmpty(tableAliasName) && tableAliasName.equals(columnTableName))
                            || (StringUtils.isNotEmpty(tableName) && tableName.equals(columnTableName))) {
                        if (isSubSelect) {
                            List<StructureQuery> subSelectList = structureQuery.getSubSelectList();
                            for (StructureQuery children : subSelectList) {
                                List<String> columns = deepStructureQuery(pageService, children);
                                columns.forEach(column ->
                                        column = column.contains(".")
                                                ? column = column.replace(column.substring(0, column.indexOf(".")), tableAliasName)
                                                : tableAliasName + "." + column
                                );
                                columnList.addAll(columns);
                            }
                            // 如果是子查询
                        } else {
                            // 不是子查询
                            List<String> columnByTableName = pageService.getColumnByTableName(tableName);
                            if (StringUtils.isNotEmpty(tableAliasName)) {
                                columnByTableName = columnByTableName.stream()
                                        .map(column -> column = tableAliasName + "." + column)
                                        .collect(Collectors.toList());
                            } else {
                                columnByTableName = columnByTableName.stream()
                                        .map(column -> column = tableName + "." + column)
                                        .collect(Collectors.toList());
                            }
                            columnList.addAll(columnByTableName);
                        }
                    }
                }
            } else if (StringUtils.isNotEmpty(aliasColumn)) {
                columnList.add(aliasColumn);
            } else {
                columnList.add(originalColumn);
            }
        }

        return columnList;
    }

}
